package com.vimi8.ebb.service;

import com.vimi8.ebb.dto.EbbDataReportCollectivityDto;
import com.vimi8.ebb.dto.TradeSituationDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * Created by think on 2017/4/10.
 */
@Mapper
public interface DataReportCollectivityService {
    /**
     * 按年份和季度获取各平台的数据情况
     * @param timeStart
     * @param timeEnd
     * @return
     */
    @Select("<script>" +
            "SELECT IFNULL(COUNT(a.id),0) AS ebbPlatformTotal,b.ThirdPartyPlatform,c.comServicePlatform,d.SelfBuiltPlatform,ad.localServicePlatform," +
            "e.logisticsNetworkNode,f.logisticsMonthIn,g.logisticsMonthOut,h.trainBatch,i.trainPeopleNum,j.productNum " +
            "FROM electricity_business AS a JOIN " +
            "(SELECT IFNULL(COUNT(id),0) AS ThirdPartyPlatform FROM electricity_business WHERE " +
            "`type`=1 " +
            "<if test=\"timeStart!=''\"> AND create_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND create_time &lt; #{timeEnd}</if>" +
            " ) AS b ON 1=1 JOIN " +
            "(SELECT IFNULL(COUNT(id),0) AS comServicePlatform FROM electricity_business WHERE " +
                    "`type`=2 " +
            "<if test=\"timeStart!=''\"> AND create_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND create_time &lt; #{timeEnd}</if>" +
            " ) AS c ON 1=1 JOIN " +
            "(SELECT IFNULL(COUNT(id),0) AS SelfBuiltPlatform  FROM electricity_business WHERE " +
            "`type`=3 " +
            "<if test=\"timeStart!=''\"> AND create_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND create_time &lt; #{timeEnd}</if>" +
            " ) AS d ON 1=1 JOIN " +


            "(SELECT IFNULL(COUNT(id),0) AS localServicePlatform  FROM electricity_business WHERE " +
            "`type`=4 " +
            "<if test=\"timeStart!=''\"> AND create_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND create_time &lt; #{timeEnd}</if>" +
            " ) AS ad ON 1=1 JOIN " +


            "(SELECT IFNULL(COUNT(id),0) AS logisticsNetworkNode FROM logistics where 1=1 " +
            "<if test=\"timeStart!='' and timeEnd!='2017-04-01'\"> AND trade_statistics_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND trade_statistics_time &lt; #{timeEnd}</if>" +
            " ) AS e ON 1=1 JOIN " +
            "(SELECT SUM(month_in_amount) AS logisticsMonthIn FROM logistics_business WHERE 1=1 " +
            "<if test=\"timeStart!='' and timeEnd!='2017-04-01'\"> AND statistics_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND statistics_time &lt; #{timeEnd}</if>" +
            " ) AS f ON 1=1 JOIN " +
            "(SELECT SUM(month_out_amount) AS logisticsMonthOut FROM logistics_business WHERE 1=1" +
            "<if test=\"timeStart!='' and timeEnd!='2017-04-01'\"> AND statistics_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND statistics_time &lt; #{timeEnd}</if>" +
            " ) AS g ON 1=1 JOIN " +
            "(SELECT IFNULL(COUNT(id),0) AS trainBatch FROM train_info WHERE 1=1" +
            "<if test=\"timeStart!='' and timeEnd!='2017-04-01'\"> AND train_date >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND train_date &lt; #{timeEnd}</if>" +
            " ) AS h ON 1=1 JOIN " +
            "(SELECT SUM(train_peoples) AS trainPeopleNum FROM train_info  WHERE 1=1 " +
            "<if test=\"timeStart!='' and timeEnd!='2017-04-01'\"> AND train_date >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND train_date &lt; #{timeEnd}</if>" +
            " ) AS i ON 1=1 JOIN " +
            "(SELECT IFNULL(COUNT(id),0) AS productNum FROM product_info  WHERE 1=1 " +
            "<if test=\"timeStart!=''\"> AND trading_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND trading_time &lt; #{timeEnd}</if>" +
            " ) AS j ON 1=1 WHERE 1=1" +
            "<if test=\"timeStart!=''\"> AND create_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND create_time &lt; #{timeEnd}</if>" +
            "</script>")
    public EbbDataReportCollectivityDto getCollectivityTotal(@Param(value = "timeStart") String timeStart,
                                                             @Param(value = "timeEnd") String timeEnd);



    @Select("<script>" +
            "SELECT * FROM ((SELECT t.`type`,t.businessVolume,TRUNCATE(t.tradingVolume,2) AS tradingVolume FROM " +
            "(SELECT e.`type`,IFNULL(SUM(p.`business_volume`),0) AS businessVolume,IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume FROM " +
            "electricity_business e " +
            "right JOIN  " +
            "product_info p ON e.id=p.`electricity_business_id` " +
            "<if test=\"timeStart!=''\"> AND p.trading_time >= #{timeStart} </if>" +
            "<if test=\"timeEnd!=''\"> AND p.trading_time &lt; #{timeEnd}  </if>" +
            " GROUP BY e.`type` ORDER BY tradingVolume DESC ) AS t WHERE t.`type` IN('1','3')) " +
            " UNION " +
            "(SELECT t.`type`,t.businessVolume,TRUNCATE(t.tradingVolume,2) AS tradingVolume FROM  " +
            "(SELECT e.`type`,IFNULL(SUM(p.`business_volume`),0) AS businessVolume,IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume FROM " +
            "electricity_business e " +
            "right JOIN  " +
            "trading_detail p ON e.id=p.`ebb_id` " +
            "<if test=\"timeStart!=''\"> AND p.trade_date >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND p.trade_date &lt; #{timeEnd} </if>" +
            " GROUP BY e.`type`  ORDER BY tradingVolume DESC) AS t WHERE t.`type` IN('2','4'))) AS tt ORDER BY tradingVolume DESC;" +
            "</script>")
    public List<TradeSituationDto> getAnalysisDataReportsByType(@Param(value = "timeStart") String timeStart,
                                                                @Param(value = "timeEnd") String timeEnd);


    @Select("<script>" +
            "SELECT #{type} as `type`,IFNULL(SUM(tradingVolume),0) AS tradingVolume,IFNULL(SUM(`businessVolume`),0) AS businessVolume FROM " +
            "((SELECT t.businessVolume,TRUNCATE(t.tradingVolume,2) AS tradingVolume FROM " +
            "(SELECT e.`type`,IFNULL(SUM(p.`business_volume`),0) AS businessVolume,IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume FROM " +
            "electricity_business e " +
            "RIGHT JOIN " +
            "product_info p ON e.id=p.`electricity_business_id` " +
            "<if test=\"timeStart!=''\"> AND p.trading_time >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND p.trading_time &lt; #{timeEnd} </if>" +
            "" +
            "GROUP BY e.`type` " +
            "" +
            "ORDER BY tradingVolume DESC) AS t WHERE t.`type` IN('1','3'))" +
            " UNION   " +
            "(SELECT t.businessVolume,TRUNCATE(t.tradingVolume,2) AS tradingVolume FROM  " +
            "(SELECT e.`type`,IFNULL(SUM(p.`business_volume`),0) AS businessVolume,IFNULL(SUM(p.`trading_volume`),0) AS tradingVolume FROM " +
            "electricity_business e " +
            "RIGHT JOIN " +
            "trading_detail p ON e.id=p.`ebb_id` " +
            "<if test=\"timeStart!=''\"> AND p.trade_date >= #{timeStart}</if>" +
            "<if test=\"timeEnd!=''\"> AND p.trade_date &lt; #{timeEnd} </if>" +
            "GROUP BY e.`type` " +
            "ORDER BY tradingVolume DESC ) AS t WHERE t.`type` IN('2','4'))) AS tt ORDER BY tradingVolume DESC;" +
            "</script>")
    public TradeSituationDto getMonthAndQuarterDataAnalysisReports(@Param(value = "timeStart") String timeStart,
                                                                   @Param(value = "timeEnd") String timeEnd,
                                                                   @Param(value = "type") String type);


}
